package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.MemVO;

public class MemDAO extends AbDao {
	private static MemDAO dao = new MemDAO();
	public static MemDAO getInstance(){
		return dao;
	}
	
	public void insertCustomer(MemVO newMember){
		Connection conn = null;
		MemVO newMemInfo = new MemVO();
		newMemInfo = newMember;
		PreparedStatement ps = null;
		
		System.out.println(newMember.getMemId());
		try{
			conn = connect();
			StringBuffer query = new StringBuffer();
			query.append("insert into tb_mem ");
			query.append("values(?,?,'USR',?,?,?,?,?,?,?,?,?,?,?,to_char(sysdate, 'YYYYMMDDHH24MISS'))");
			
			ps = conn.prepareStatement(query.toString());
			ps.setString(1, newMemInfo.getMemId());
			ps.setString(2, newMemInfo.getMemNm());
			ps.setString(3, newMemInfo.getPwd());
			ps.setString(4, newMemInfo.getPwdHint());
			ps.setString(5, newMemInfo.getPwdHintAns());
			ps.setString(6, newMemInfo.getSsn());
			ps.setString(7, newMemInfo.getBirthDt());
			ps.setString(8, newMemInfo.getMaleFlg());
			ps.setString(9, newMemInfo.getTelNo());
			ps.setString(10, newMemInfo.getCellNo());
			ps.setString(11, newMemInfo.getAddr());
			ps.setString(12, newMemInfo.getEmail());
			ps.setString(13, newMemInfo.getEmailRcvFlg());
			ps.executeUpdate();

		}catch(Exception ex){
			System.out.println("insertCustomer exception : " + ex);
		}
	}
	
	
	public Vector<MemVO> getCustomer(){
		Vector<MemVO> listVO = new Vector<MemVO> ();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		try{
			conn = connect();
			
			String query = "select * from tb_mem";
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				MemVO resultMem = new MemVO();
				resultMem.setMemId(rs.getString("mem_id"));
				resultMem.setMemNm(rs.getString("mem_nm"));
				resultMem.setMemCd(rs.getString("mem_cd"));
				resultMem.setPwd(rs.getString("pwd"));
				resultMem.setPwdHint(rs.getString("pwd_hint"));
				resultMem.setPwdHintAns(rs.getString("pwd_hint_ans"));
				resultMem.setSsn(rs.getString("ssn"));
				resultMem.setBirthDt(rs.getString("birth_dt"));
				resultMem.setMaleFlg(rs.getString("male_flg"));
				resultMem.setTelNo(rs.getString("tel_no"));
				resultMem.setCellNo(rs.getString("cell_no"));
				resultMem.setAddr(rs.getString("addr"));
				resultMem.setEmail(rs.getString("email"));
				resultMem.setEmailRcvFlg(rs.getString("email_rcv_flg"));
				resultMem.setMemRegDtm(rs.getString("mem_reg_dtm"));
				System.out.println(resultMem.getMemNm());
				listVO.add(resultMem);
			}
			
			for(int i = 0; i < listVO.size(); i++)
			{
				System.out.println(listVO.elementAt(i).getMemNm());
			}
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
		}
		
		return listVO;
	}
	public MemVO getCustomer(String customerId){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");
						
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, customerId);
			rs = ps.executeQuery();
			while(rs.next()){
				MemVO vo = new MemVO();
				
			    vo.setMemId(rs.getString(1));
			    vo.setMemNm(rs.getString(2));
			    vo.setMemCd(rs.getString(3));
			    vo.setPwd(rs.getString(4));
			    vo.setPwdHint(rs.getString(5));
			    vo.setPwdHintAns(rs.getString(6));
			    vo.setSsn(rs.getString(7));
			    vo.setBirthDt(rs.getString(8));
			    vo.setMaleFlg(rs.getString(9));
			    vo.setTelNo(rs.getString(10));
			    vo.setCellNo(rs.getString(11));
			    vo.setAddr(rs.getString(12));
			    vo.setEmail(rs.getString(13));
			    vo.setEmailRcvFlg(rs.getString(14));
			    vo.setMemRegDtm(rs.getString(15));
			    
				return vo;
			}
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
			close(conn, ps, rs);
		}
		
		return null;
	} // getCustomer
	
	
	public MemVO getCustomer(String memId, String pwd){
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();
			
			StringBuffer query = new StringBuffer();
			query.append("SELECT mem_id, mem_nm, mem_cd, pwd, pwd_hint, pwd_hint_ans, ssn, birth_dt, ");
			query.append("        male_flg, tel_no, cell_no, addr, email, email_rcv_flg, mem_reg_dtm ");
			query.append("FROM tb_mem ");
			query.append("WHERE mem_id = ? ");
			query.append("AND   pwd = ? ");
						
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, memId);
			ps.setString(2, pwd);
			rs = ps.executeQuery();
			while(rs.next()){
				MemVO vo = new MemVO();
				
			    vo.setMemId(rs.getString(1));
			    vo.setMemNm(rs.getString(2));
			    vo.setMemCd(rs.getString(3));
			    vo.setPwd(rs.getString(4));
			    vo.setPwdHint(rs.getString(5));
			    vo.setPwdHintAns(rs.getString(6));
			    vo.setSsn(rs.getString(7));
			    vo.setBirthDt(rs.getString(8));
			    vo.setMaleFlg(rs.getString(9));
			    vo.setTelNo(rs.getString(10));
			    vo.setCellNo(rs.getString(11));
			    vo.setAddr(rs.getString(12));
			    vo.setEmail(rs.getString(13));
			    vo.setEmailRcvFlg(rs.getString(14));
			    vo.setMemRegDtm(rs.getString(15));
			    
				return vo;
			}
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
			close(conn, ps, rs);
		}
		
		return null;
	} // getCustomer
	
	public void modifyUser(MemVO vo){
		
		Connection conn = null;
		PreparedStatement ps = null;
		//ResultSet rs = null;
		try{
			conn = connect();
			
			
			StringBuffer query = new StringBuffer();
			query.append("UPDATE tb_mem ");
			query.append("set mem_nm=? , pwd=? , pwd_hint=? ");
			query.append(",pwd_hint_ans=? , ssn=? , birth_dt=? ");
			query.append(",male_flg=? , tel_no=? , cell_no=? ");
			query.append(",addr=? ,email=?,email_rcv_flg=? ");
			query.append("WHERE mem_id = ? ");
									
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, vo.getMemNm());
		    ps.setString(2, vo.getPwd());
		    ps.setString(3, vo.getPwdHint());
		    ps.setString(4, vo.getPwdHintAns());
		    ps.setString(5, vo.getSsn());
		    ps.setString(6, vo.getBirthDt());
		    ps.setString(7, vo.getMaleFlg());
		    ps.setString(8, vo.getTelNo());
		    ps.setString(9, vo.getCellNo());
		    ps.setString(10, vo.getAddr());
		    ps.setString(11, vo.getEmail());
		    ps.setString(12, vo.getEmailRcvFlg());
		    ps.setString(13, vo.getMemId());
		    		    
		
			ps.executeUpdate();
			System.out.println("db updating...");
		
		}catch(Exception ex){
			System.out.println("getCustomer exception : " + ex);
			close(conn, ps);
		}
	}
}

